package cn.alexy.dbbench.server.repository;
import cn.alexy.dbbench.server.entity.Pi;
import cn.alexy.dbbench.server.service.SumTemp;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface PiDao extends JpaRepository<Pi, Long> {
	
	@Query("select new cn.alexy.dbbench.server.service.SumTemp(substring(pi.scol,1,:n),count(pi),avg(pi.dcol),max(pi.scol)) from Pi pi group by substring(pi.scol,1,:n) order by substring(pi.scol,1,:n)")
	public List<SumTemp> runQuery( @Param ("n") int n);
	
	@Query("select new cn.alexy.dbbench.server.service.SumTemp(substring(pi.scol,1,4),count(pi),avg(pi.dcol),max(pi.scol)) from Pi pi group by substring(pi.scol,1,4) order by substring(pi.scol,1,4)")
	public List<SumTemp> runQuery();
	
//	public void deletePiByScol(String s);

}
